package simorg.domain.persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import simorg.domain.Course;
import simorg.util.DatabaseConnection;

public class CourseTable {

	
	public static void bootstrap(boolean drop) throws Exception {
		DatabaseConnection dc = new DatabaseConnection();
		try {
			Statement stmt = null;
		    String query = null;
		    if ( drop ) {
		    	query = "DROP TABLE IF EXISTS COURSE";
		    	stmt = dc.getConnection().createStatement();
			    stmt.execute(query);
		    }
		    query = "CREATE TABLE IF NOT EXISTS COURSE" +
		    	" (ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(20), SEMESTER VARCHAR(50), SECTION VARCHAR(50), " + 
		    	"INSTRUCTOR_ID INT)";
		    stmt = dc.getConnection().createStatement();
		    stmt.execute(query);
		   
		    Course c = new Course();
			c.setName("CMPS 147");
			c.setSemester("Spring 2011");
			c.setSection("001");
			c.setInstructorID(2);
			save(dc, c);
			
			c = new Course();
			c.setName("CMPS 147");
			c.setSemester("Spring 2011");
			c.setSection("002");
			c.setInstructorID(3);
			save(dc, c);
			
			c = new Course();
			c.setName("CMPS 147");
			c.setSemester("Spring 2011");
			c.setSection("003");
			c.setInstructorID(4);
			save(dc, c);
			
			c = new Course();
			c.setName("CMPS 148");
			c.setSemester("Spring 2011");
			c.setSection("001");
			c.setInstructorID(2);
			save(dc, c);
			
		    System.out.println("Bootstrapped Courses");
		    Collection<Course> courses = findAll(dc);
		    for ( Course cs : courses ) {
		    	System.out.println("ID:  " + cs.getId() + " - " + cs.getName());
		    }
		}
		finally {
			dc.getConnection().close();
		}
	}
	
	public static void save(DatabaseConnection dc, Course course) {
		try {
			String query = null;
			if ( course.getId() < 0 ) {
				query = "INSERT INTO COURSE (NAME, SEMESTER, SECTION, INSTRUCTOR_ID) " +
								"VALUES ( ?, ?, ?, ?)";
				PreparedStatement insert = dc.getConnection().prepareStatement(query);
				CourseTable.fillPreparedStatement(course, insert);
				insert.executeUpdate();
				ResultSet rs = insert.getGeneratedKeys();
				while (rs.next()){
					course.setId(rs.getInt(1));
				}
			}
			else {
				// update existing course
				query = "UPDATE COURSE SET NAME=?, SEMESTER = ?, SECTION = ?, INSTRUCTOR_ID = ? " +
						"WHERE ID=?";
				PreparedStatement update = dc.getConnection().prepareStatement(query);
				CourseTable.fillPreparedStatement(course, update);
				update.setInt(5, course.getId());
				update.executeUpdate();
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static Collection<Course> findAll(DatabaseConnection db){
		QueryConstraints constraints = new QueryConstraints();
		return find(db, constraints);
	}
	
	public static void fillFromResultSet(Course c,ResultSet rs) throws Exception {
		c.setId(rs.getInt("ID"));
		c.setName(rs.getString("NAME"));
		c.setSemester(rs.getString("SEMESTER"));
		c.setSection(rs.getString("SECTION"));
		c.setInstructorID(rs.getInt("INSTRUCTOR_ID"));
	}

	public static void fillPreparedStatement(Course c, PreparedStatement stmt) throws Exception {
		stmt.setString(1, c.getName());
		stmt.setString(2, c.getSemester());
		stmt.setString(3, c.getSection());
		stmt.setInt(4, c.getInstructorID());
	}
	
	public static ArrayList<Course> find(DatabaseConnection dc, QueryConstraints constraints) {
		ArrayList<Course> retval = new ArrayList<Course>();
		try {
			String query = "SELECT * FROM COURSE " + constraints.getParameterString();
			PreparedStatement stmt = dc.getConnection().prepareStatement(query);
			constraints.fillPreparedStatement(stmt);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()){
				Course course = new Course();
				CourseTable.fillFromResultSet(course, rs);
		        retval.add(course);
		    }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return retval;
	}

	
	public static void deleteById(DatabaseConnection db, int id)  {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("ID", id));
		try {
			String query = "DELETE FROM COURSE " + constraints.getParameterString();
			PreparedStatement stmt = db.getConnection().prepareStatement(query);
			constraints.fillPreparedStatement(stmt);
			stmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static Course findById(DatabaseConnection db, int id) {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("ID", id));
		ArrayList<Course> result = find(db, constraints);
		return result.size() > 0 ? result.get(0) : null;
	}
}
